# Created by Katie Cunningham
# This script interfaces with a Trac database to get various reports
# on tickets during a certain date range, for a certain milestone.
# It also returns a summary of all tickets for a chosen milestone.

# Requires pysqlite2

from datetime import *
from time import mktime

import sqlite
import os
import sys

def openDB(dbname):
	"""Opens a database and returns a connection to it, given the name of that database. Exits the program upon failure."""
        dir = os.listdir(os.getcwd())
        databases = []
        for thing in dir:
                if thing.endswith("db"):
                        databases.append(thing)
        if dbname in databases:
                con = sqlite.connect(dbname, autocommit=1)
        else:
                print "That's not a database! Go make it first!"
                sys.exit()
        return con

def printTix(tix):
	"""For each ticket in an array, it will print all the information for that ticket on one line."""
	print "Number of tickets: ", tix.__len__()
	for ticket in tix:
		ticketline = ""
		for item in ticket:
			ticketline = ticketline + str(item) + "\t"
		print ticketline

def getChangedTix(cur, start, end, milestone):
	"""Returns all tickets that have been changed in a certain date range, of a certain milestone."""
	sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end) + " and milestone = '" + milestone + "'"
	cur.execute(sqlcmd)
	tix = cur.fetchall()
	printTix(tix)
	
def getTickets(cur, milestone):
	"""Returns all tickets of a certain milestone"""
	sqlcmd = "select id from tickets where milestone = '" + milestone + "'"
	cur.execute(sqlcmd)
	tix = cur.fetchall()
	return tix

def getMilestoneOverview(cur, milestone):
	"""Returns a summary of how many tickets are in a given milestone, by status."""
	sqlcmd = "select distinct status from ticket"
	cur.execute(sqlcmd)
	statuses = cur.fetchall()
	for status in statuses:
		sqlcmd = "select id from ticket where milestone = '" + milestone + "' and status = '" + status[0] + "'"
		cur.execute(sqlcmd)
		tix = cur.fetchall()
		print status[0], ": ", tix.__len__()

def getUnixTime(dtime):
	"""Converts a timestamp to unixtime"""
	utime = int(mktime(dtime.timetuple())+1e-6*dtime.microsecond)
	return utime

def getClosedTix(cur, start, end, milestone):
	"""Gets all tickets that were closed within a certain daterange, of a certain milestone."""
    sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end)+" and ch.newvalue = 'closed' and milestone = '" + milestone + "'"
    cur.execute(sqlcmd)
    tix = cur.fetchall()
    printTix(tix)

def getOpenedTix(cur, start, end, milestone):
	"""Gets all tickets that were opened within a certain date range, given a certain milestone"""
    sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where t.time > "+str(start)+" and t.time < "+str(end)+ " and milestone = '" + milestone + "'"
    cur.execute(sqlcmd)
    tix = cur.fetchall()
	printTix(tix)

def getTestingTix(cur, start, end, milestone):
	"""Gets all tickets that were moved to testing within a certain date range, given a certain milestone."""
    sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end) + " and milestone = '" + milestone + "' and (ch.newvalue like '%verify%' or ch.newvalue = '%test%')"
    cur.execute(sqlcmd)
    tix = cur.fetchall()
    printTix(tix)

def getTimeFromUser():
	"""Gets a date from the user"""
	year = int(raw_input("Year: "))
	month = int(raw_input("Month: "))
	day = int(raw_input("Day: "))
	date = str(year)+"-"+str(month)+"-"+str(day)
	return datetime.strptime(date, "%Y-%m-%d")

def interactive(unixStart, unixEnd, dbname):
	"""An interactive opener for the program. When you actually want input from  the user, have this in the __main__ part of the program. The user will be asked for two dates and a database name."""
    print "Welcome to the ticket change thinger"
    startDate = getTimeFromUser()
    print startDate
    unixStart = getUnixTime(startDate)
    endDate = getTimeFromUser()
    print endDate
    unixEnd = getUnixTime(endDate)
    dbname = raw_input("Database name: ")
	return unixStart, unixEnd, dbname

def nonInteractive(unixStart, unixEnd, dbname):
	"""An non-interactive opener. Supplies dates and a database."""
    print "Welcome to the ticket change thinger"
    unixStart = 1214870400
    unixEnd = 1217548800
    dbname = "trac.db"
	return unixStart, unixEnd, dbname


def printResults(cur, unixStart, unixEnd, milestone):
	"""Prints the reports for all queries."""
	print "Tickets changed:"
    getChangedTix(cur, unixStart, unixEnd, milestone)
	print "\n\n\n"
    print "Tickets opened:"
    getOpenedTix(cur, unixStart, unixEnd, milestone)
    print "\n\n\n"
    print "Tickets closed:"
    getClosedTix(cur, unixStart, unixEnd, milestone)
    print "\n\n\n"
    print "Tickets moved to testing:"
    getTestingTix(cur, unixStart, unixEnd, milestone)
    print "\n\n\n"
    print "Overview for current milestone:"	
	getMilestoneOverview(cur, milestone)
	return cur

def getMilestones(cur):
	"""Gets all milestones, then has the user choose one. That milestone is returned."""
	sqlcmd = "select distinct milestone from ticket"
	cur.execute(sqlcmd)
	milestones = cur.fetchall()
	milestones.sort()
	for milestone in milestones:
		print str(milestones.index(milestone)), " - ", milestone[0]
	msNum = raw_input("Which milestone (#): ")
	milestone = milestones[int(msNum)]
	milestone = milestone[0]
	return milestone
	
def getHourFieldContents(cur, milestone):
	"""Gets all of the contents of the hours field, given a certain milestone. Returns that raw data."""
	sqlcmd = "select c.value from ticket_custom c left join ticket t on (ticket = id) where t.milestone = '" + milestone + "'"
	cur.execute(sqlcmd)
	hours = cur.fetchall()
	return hours

def cleanHoursArray(hours):
	"""Does a LOT of cleaning of the raw hours array. In the end, you get an array with all hour estimates in their own array item, with this format: user, [hour-low, hour-high] (if the user provided a low-high estimate) or user, [hour] (if the user proviced just one number"""
	print "Coming in: "
	print hours
	tmp = hours
	hours = []
	for item in tmp:
		hours.append(item[0])

	print "Pass one: "
	print hours
	tmp = hours
	hours = []
	for item in tmp:
		if item.__contains__('='):
			hours.append(item)
	
	print "Pass two: ", hours
	tmp = hours
	hours = []
	newline = '\r\n'
	for item in tmp:
		if item.__contains__(newline):
			s = item.split(newline)
			for hour in s:
				hours.append(hour)
		else:
			hours.append(item)
	for item in hours:
		if item.__contains__(':'):
			line = item.strip(':')
			i = hours.index(item)
			hours[i] = line
	
	print "Pass three: ", hours
	tmp = hours
	hours = []
	for item in tmp:
		hours.append(item.split('='))
	
	if hours.__contains__(['']):
		for i in range(0,hours.count([''])):
			hours.remove([''])
			print "Removed."	

	print "Pass four: ", hours
	tmp = hours
	hours = []
	for item in tmp:
		print item
		s = str(item[1])
		s = s.strip(' ')
		s = s.split('-')
		item[1] = s
		hours.append(item)
	return hours

def totalHours(hours):
	"""Calculates total hours. Returns a dictionary with all hours, with high and low estimates by user."""
	hourDict = {}
	for item in hours:
		item[0] = item[0].strip(' ')
		if not hourDict.has_key(item[0]):
			hourDict[item[0]] = [0,0]
	for item in hours:
		if item[1].__len__() == 1:
			hourDict[item[0]][0] = hourDict[item[0]][0] + float(item[1][0])
                        hourDict[item[0]][1] = hourDict[item[0]][1] + float(item[1][0])
		else:
                        hourDict[item[0]][0] = hourDict[item[0]][0] + float(item[1][0])
                        hourDict[item[0]][1] = hourDict[item[0]][1] + float(item[1][1])
	return hourDict

def getTicketHours(cur, milestone):
	sqlcmd = "select t.id, t.summary, c.value from ticket t left join ticket_custom c on t.id = c.ticket where t.milestone = '" + milestone + "' and c.name = 'hours_estimate'"
	cur.execute(sqlcmd)
	tickets = cur.fetchall()
	return tickets

def cleanTickets(tickets):
	# First, making the immutable set into an array
	tix = []
	for ticket in tickets:
		t = []
		for item in ticket:
			t.append(item)
		tix.append(t)

	# Now, splitting third array element into individual bits
	tmp = tix
	tix = []
	for ticket in tmp:
		hours = ticket[2]
		hours = hours.split("\r\n")
		tix.append([ticket[0], ticket[1], hours])

	# Now, we just need the numbers
	tmp = tix
	tix = []
	for ticket in tmp:
		hours = ticket[2]
		tmp = []
		for person in hours:
			person = person.strip(':')
			if person.__contains__('='):
				person = person.split('=')[1]
			person = person.strip(' ')
			tmp.append(person)
		hours = tmp
		tix.append([ticket[0], ticket[1], hours])
	# Some tickets have high low estimates. Therefore, we need to have
	# high lows for all.
	tmp = tix
	tix = []
	for ticket in tmp:
		tmpItems = []
		for item in ticket[2]:
			if item.__contains__("-"):
				item = item.split("-")
			elif item == '':
				item = ['0', '0']
			else:
				item = [item, item]
			tmpItems.append(item)
		tix.append([ticket[0], ticket[1], tmpItems])
	return tix

def printTotals(tix):
	print "Ticket\tLow\tHigh\tSummary"
	for ticket in tix:
		print ticket[0], "\t",
		low = 0
		high = 0
		for hour in ticket[2]:
			low = low + float(hour[0])
			high = high + float(hour[1])
		print int(low),"\t", int(high), "\t", ticket[1]
		
def getChangedTixByField(cur, unixStart, unixEnd):
	"""Returns an array of all tickets that changed within a date range, where a certain field had changed. Includes all users, all milestones."""
	field = getFields(cur)
	sqlcmd = "select ch.ticket, t.summary, ch.oldvalue, ch.newvalue from ticket_change ch left join ticket t on (t.id = ch.ticket) where ch.field = '" + field + "' and ch.time > " + str(unixStart) + " and ch.time < "+ str(unixEnd)
	cur.execute(sqlcmd)
	tix = cur.fetchall()
	return tix
	
def getFields(cur):
	"""Prints all the available fields and returns one chosen by the user"""
	sqlcmd = "select distinct field from ticket_change"
	cur.execute(sqlcmd)
	fields = cur.fetchall()
	fields.sort()
	for field in fields:
		print fields.index(field), " - ", field[0]
	fieldnum = raw_input("Field #: ")
	return fields[int(fieldnum)][0]
